package com.af.common;


import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;


public class DBHelper {

	private Class beanClass = null;
	private QueryRunner run = null;
	private DataSource ds = null;
	private Connection conn = null;
	static DBHelper instance=null;
	
	

	/**
	 * 设置数据连接
	 * 
	 * @throws Exception
	 */
	
	public static synchronized DBHelper getInstance() {
		instance = new DBHelper("jdbc/live");
		return instance;
	}
	
	public DBHelper(String env){
		try {
			ds = (DataSource) new InitialContext().lookup("java:comp/env/"+env);
			run = new QueryRunner(ds);
			conn = ds.getConnection();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
//			throw new NamingException("NamingException in [jdbc/business]\n"
//					+ e.getMessage());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	/**
	 * 根据无参sql语句进行查询，并返回一个对象，用于单条记录的查询
	 * 
	 * @param sql
	 * @return Object
	 * @throws Exception
	 */
	public Object query(String sql) throws SQLException {
		if (ds == null || beanClass == null)
			return null;

		ResultSetHandler h = new BeanHandler(beanClass);
		Object result = null;
		try {
			result = run.query(sql, h);
		} catch (SQLException e) {
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}

	/**
	 * 根据有参sql语句进行查询，并返回一个对象，用于单条记录的查询
	 * 
	 * @param sql
	 * @param args
	 * @return Object
	 * @throws Exception
	 */
	public Object query(String sql, Object... args) throws SQLException {
		if (ds == null || beanClass == null)
			return null;

		ResultSetHandler h = new BeanHandler(beanClass);
		Object result = null;

		try {
			result = run.query(sql, args, h);
		} catch (SQLException e) {
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}

	/**
	 * 根据无参sql语句进行查询，并返回一个数据集，用于多条记录的查询
	 * 
	 * @param sql
	 * @return ArrayList
	 * @throws Exception
	 */
	public ArrayList queryRecords(String sql) throws SQLException {
		if (ds == null || beanClass == null)
			return null;

		ResultSetHandler h = new BeanListHandler(beanClass);
		ArrayList result = null;

		try {
			result = (ArrayList) run.query(sql, h);
			// obtain the quaried records
		} catch (SQLException e) {
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}

		return result;
	}

	/**
	 * 根据有参sql语句进行查询，并返回一个数据集，用于多条记录的查询
	 * 
	 * @param sql
	 * @param args
	 * @return
	 * @throws Exception
	 */
	public ArrayList queryRecords(String sql, Object... args)
			throws SQLException {
		if (ds == null || beanClass == null)
			return null;

		ResultSetHandler h = new BeanListHandler(beanClass);
		ArrayList result = null;

		try {
			result = (ArrayList) run.query(sql, args, h);

			// obtain the quaried records
		} catch (SQLException e) {
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}

		return result;
	}

	/**
	 * 根据无参sql语句进行(map)查询，并返回一个数据集，用于多条记录的查询
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public List queryRecordsMap(String sql) throws SQLException {
		List result = null;
		try {
			result = (List) run.query(sql, new MapListHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}

	public Object queryRecordMap(String sql) throws SQLException {
		Object result = null;
		try {
			result = (Object) run.query(sql, new MapHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}

	/**
	 * 根据有参sql语句进行(map)查询，并返回一个数据集，用于多条记录的查询
	 * 
	 * @param sql
	 * @param args
	 * @return
	 * @throws Exception
	 */
	public List queryRecordsMap(String sql, Object... args) throws SQLException {
		List result = null;
		try {
			result = (List) run.query(sql, args, new MapListHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}

	public Object queryRecordMap(String sql, Object... args)
			throws SQLException {
		Object result = null;
		try {
			result = (Object) run.query(sql, args, new MapHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}

	/**
	 * 根据无参sql语句进行数据更新，并返回更新后影响的记录数
	 * 
	 * @param sql
	 * @return int
	 * @throws SQLException
	 */
	public int update(String sql) throws SQLException {
		if (ds == null)
			return 0;

		int result = 0;

		try {
			result = run.update(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}

		return result;
	}

	/**
	 * 根据有参sql语句进行数据更新，并返回更新后影响的记录数
	 * 
	 * @param sql
	 * @param args
	 * @return int
	 * @throws SQLException
	 */
	public int update(String sql, Object... args) throws SQLException {
		if (ds == null)
			return -1;

		int result = 0;

		try {
			result = run.update(sql, args);
		} catch (SQLException e) {
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}
	/**
	 * 设置存放查询结果的Bean类，每个Bean类对象对应一条查询的结果记录
	 * 
	 * @param beanClass
	 *            ，如User.class
	 */
	public void setBeanClass(Class beanClass) {
		this.beanClass = beanClass;
	}

	/**
	 * 资源释放
	 * 
	 * @throws SQLException
	 */
	public void close() {
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	@Override
	protected void finalize() throws Throwable {
		// TODO Auto-generated method stub
		close();
		super.finalize();
	}
}
